/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.tspp.dao;

import com.tspp.common.ResourceManager;
import com.tspp.dao.dto.Application;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Concrete realization of applicationDao interface. Retrieve the connection from pool in every method
 * @author Maks
 */
public class ApplicationDaoImpl extends AbstractDao<Application> implements ApplicationDao {

    /** All finder methods in this class use this SELECT constant to build their queries*/
    private static final String SQL_SELECT = "SELECT ID_ENTRANT, ID_SPECIALITY, ACCEPTED FROM " + getTableName() + "";

    /** SQL INSERT statement for this table */
    private static final String SQL_INSERT = "INSERT INTO " + getTableName() + " ( ID_ENTRANT, ID_SPECIALITY, ACCEPTED ) VALUES ( ?, ?, ? )";

    /** SQL UPDATE statement for this table*/
    private static final String SQL_UPDATE = "UPDATE " + getTableName() + " SET ACCEPTED = ? WHERE ID_ENTRANT = ? AND ID_SPECIALITY = ?";

    /** SQL DELETE statement for this table*/
    private static final String SQL_DELETE = "DELETE FROM " + getTableName() + " WHERE ID_ENTRANT = ? AND ID_SPECIALITY = ?";

    /** Index of column ID_ENTRANT*/
    private static final int COLUMN_ID_ENTRANT = 1;

    /** Index of column ID_SPECIALITY*/
    private static final int COLUMN_ID_SPECIALITY = 2;

    /** Index of column ACCEPTED*/
    private static final int COLUMN_ACCEPTED = 3;

    /**
     * Method 'getTableName'
     *
     * @return String name of table
     */
    public static String getTableName() {
        return "APPLICATION";
    }

    /**
     * Inserts a new row in the APPLICATION table.
     */
    public void insert(Application dto) throws DaoException {
        // get a connection from the ResourceManager
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            // get a connection from the ResourceManager
            conn = ResourceManager.getConnection();
            stmt = conn.prepareStatement(SQL_INSERT);
            int index = 1;
            stmt.setInt(index++, dto.getIdEntrant());
            stmt.setInt(index++, dto.getIdSpeciality());
            stmt.setInt(index++, dto.isAccepted() ? 1 : 0);
            //System.out.println("Executing " + SQL_INSERT + " with DTO: " + dto);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }
    }

    /**
     * Updates a single row in the APPLICATION table.
     */
    public void update(int speciality, int entrant, Application dto) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            // get a connection from the ResourceManager
            conn = ResourceManager.getConnection();
            //System.out.println("Executing " + SQL_UPDATE + " with DTO: " + dto);
            stmt = conn.prepareStatement(SQL_UPDATE);
            int index = 1;
            stmt.setInt(index++, dto.isAccepted() ? 1 : 0);
            stmt.setInt(index++, entrant);
            stmt.setInt(index++, speciality);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }

    }

    /**
     * Deletes a single row in the APPLICATION table.
     */
    public void delete(int speciality, int entrant) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            //get a connection from the ResourceManager
            conn = ResourceManager.getConnection();
            //System.out.println("Executing " + SQL_DELETE + " with PK: " + pk);
            stmt = conn.prepareStatement(SQL_DELETE);
            stmt.setInt(1, entrant);
            stmt.setInt(2, speciality);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }
    }

    /**
     * Returns all rows from the APPLICATION table that match the criteria 'ID_ENTRANT = :idEntrant AND ID_SPECIALITY = :idSpeciality'.
     */
    public Application findByPrimaryKey(int idEntrant, int idSpeciality) throws DaoException {
        List<Application> app = findByDynamicSelect(SQL_SELECT + " WHERE ID_ENTRANT = ? AND ID_SPECIALITY = ?", new Object[]{idEntrant, idSpeciality});
        return app.isEmpty() ? null : app.get(0);
    }

    /**
     * Returns all rows from the APPLICATION table that match the criteria ''.
     */
    public List<Application> findAll() throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " ORDER BY ID_ENTRANT, ID_SPECIALITY", null);
    }

    /**
     * Returns all rows from the APPLICATION table that match the criteria 'ID_ENTRANT = :idEntrant'.
     */
    public List<Application> findByEntrant(int idEntrant) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ID_ENTRANT = ?", new Object[]{new Integer(idEntrant)});
    }

    /**
     * Returns all rows from the APPLICATION table that match the criteria 'ID_SPECIALITY = :idSpeciality'.
     */
    public List<Application> findBySpeciality(int idSpeciality) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ID_SPECIALITY = ?", new Object[]{new Integer(idSpeciality)});
    }

    /**
     * Returns all rows from the APPLICATION table that match the criteria 'ACCEPTED = :accepted'.
     */
    public List<Application> findWhereAcceptedEquals(boolean accepted) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ACCEPTED = ? ORDER BY ACCEPTED", new Object[]{new Integer(accepted ? 1 : 0)});
    }

   /**
     * Implements abstract getDtoFrom of AbstractDao
     * @see AbstractDAO
     * @param rs
     * @return newly created transfer object
     * @throws SQLException
     */
    protected Application getDtoFrom( ResultSet rs) throws SQLException {
        Application dto = new Application();
        dto.setIdEntrant(rs.getInt(COLUMN_ID_ENTRANT));
        dto.setIdSpeciality(rs.getInt(COLUMN_ID_SPECIALITY));
        dto.setAccepted(rs.getInt(COLUMN_ACCEPTED) == 0 ? false : true);
        return dto;
    }
}

